{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SUM and COUNT\n",
    "\n",
    "## World Country Profile: Aggregate functions\n",
    "This tutorial is about aggregate functions such as COUNT, SUM and AVG. An aggregate function takes many values and delivers just one value. For example the function SUM would aggregate the values 2, 4 and 5 to deliver the single value 11."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Total world population\n",
    "\n",
    "Show the total population of the world.\n",
    "\n",
    "```\n",
    "world(name, continent, area, population, gdp)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7118632738</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('7118632738'),)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(population) FROM world;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. List of continents\n",
    "\n",
    "List all the continents - just once each."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>continent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Caribbean</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eurasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Europe</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Caribbean',),\n",
       " ('Asia',),\n",
       " ('South America',),\n",
       " ('North America',),\n",
       " ('Oceania',),\n",
       " ('Eurasia',),\n",
       " ('Africa',),\n",
       " ('Europe',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DiSTINCT continent FROM world;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. GDP of Africa\n",
    "\n",
    "Give the total GDP of Africa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1811788000000</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('1811788000000'),)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(gdp) FROM world WHERE continent = 'Africa';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Count the big countries\n",
    "\n",
    "How many countries have an **area** of at least 1000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(29,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(name) FROM world\n",
    "WHERE area >= 1000000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Baltic states population\n",
    "\n",
    "What is the total **population** of ('Estonia', 'Latvia', 'Lithuania')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6251750</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('6251750'),)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(population) FROM world\n",
    "WHERE name IN ('Estonia', 'Latvia', 'Lithuania');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Counting the countries of each continent\n",
    "\n",
    "For each **continent** show the **continent** and number of countries.\n",
    "\n",
    "> _Using GROUP BY and HAVING_   \n",
    "> You may want to look at these examples: [Using GROUP BY and HAVING](https://sqlzoo.net/wiki/Using_GROUP_BY_and_HAVING.)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>continent</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Caribbean</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Asia</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South America</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>North America</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oceania</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eurasia</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Africa</td>\n",
       "        <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Europe</td>\n",
       "        <td>44</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Caribbean', 11),\n",
       " ('Asia', 47),\n",
       " ('South America', 13),\n",
       " ('North America', 11),\n",
       " ('Oceania', 14),\n",
       " ('Eurasia', 2),\n",
       " ('Africa', 53),\n",
       " ('Europe', 44)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT continent, COUNT(name) FROM world\n",
    "GROUP BY continent;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Counting big countries in each continent\n",
    "\n",
    "For each **continent** show the **continent** and number of countries with populations of at least 10 million."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>continent</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Caribbean</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Asia</td>\n",
       "        <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South America</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>North America</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eurasia</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oceania</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Africa</td>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Europe</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Caribbean', 2),\n",
       " ('Asia', 26),\n",
       " ('South America', 8),\n",
       " ('North America', 4),\n",
       " ('Eurasia', 1),\n",
       " ('Oceania', 1),\n",
       " ('Africa', 29),\n",
       " ('Europe', 14)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT continent, COUNT(name) FROM world\n",
    "  WHERE population >= 10000000\n",
    "    GROUP BY continent;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Counting big continents\n",
    "\n",
    "List the continents that **have** a total population of at least 100 million."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>continent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eurasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Europe</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Asia',),\n",
       " ('South America',),\n",
       " ('North America',),\n",
       " ('Eurasia',),\n",
       " ('Africa',),\n",
       " ('Europe',)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT continent FROM world\n",
    "GROUP BY continent\n",
    "HAVING SUM(population) >= 100000000;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
